   // backend/transform/transform.js
const ExcelJS = require('exceljs');
const fs = require('fs');
const path = require('path');
const xlsx = require("xlsx");
const {ProductInfo} = require("../model/ProductInfo")
const FormulaParser = require('hot-formula-parser').Parser;
const moment = require("moment")
      
//    async function caculateFormulas(filePath) {
//         // console.log(`file path is : ${filePath}`)

//         var currentSheet = {}
    
//         const parser = new FormulaParser();
    
//         parser.on('callCellValue',(cellCoord,done)=>{
//             var value = currentSheet.getCell(cellCoord.label).value     
//             if(value.formula){
//                 value = parser.parse(value.formula).result;
//             }
//             // console.log(`cell location ${cellCoord.label} value :${value}`)
//             done(value)
//         })    
 

//        const workbook = new ExcelJS.Workbook();
//        await workbook.xlsx.readFile(filePath)
   
//        workbook.eachSheet((sheet,sheetId)=>{
           
//            currentSheet = sheet;
//            // console.log(`Caculating formulas in sheet: ${sheet.name}`);
//            sheet.eachRow((row,rowNum)=>{
//                row.eachCell((cell,colNumber)=>{
//                 // console.log(JSON.stringify(cell.value||{}))
//                    if(cell.formula||cell.sharedFormula){
//                        const formula = cell.formula||cell.sharedFormula
//                        // console.log(`Formula in cell ${cell.address} : ${cell.formula}`);
//                        const value = parser.parse(formula).result||""
//                     //    console.log(`Caculated formula: ${cell.formula} value: ${value}`)
//                        cell.value = value
//                    }else{
//                         // console.log(`plain text value is: ${cell.value}`)
//                    }
//                })
//            })
//        })
   
//        await workbook.xlsx.writeFile(filePath);
//    }

const calculateSum = (arr) => {
    var sum =  arr.reduce((total, current) => {
        return total + current;
    }, 0);
    return (sum*1.0).toFixed(2)
}

const transformExcel = async (req, res) => {
    var filePath = req.file.path;
//    filePath = path.resolve(__dirname,"..","uploads","data.xls")

    // Log the file path for debugging
   console.log(`Received file path: ${filePath}`);

    // Check if the file exists
    if (!fs.existsSync(filePath)) {
        console.error('File not found:', filePath);
        return res.status(400).send('File not found. Please ensure the file was uploaded correctly.');
    }

    // Check if the file is an Excel file based on extension
    const extname = path.extname(req.file.originalname).toLowerCase();

    if (extname !== '.xlsx' && extname !== '.xls') {
        return res.status(400).send('Invalid file type. Please upload an Excel file (.xlsx or .xls).');
    }
    
    const workbook = new ExcelJS.Workbook();       

    try {
        await workbook.xlsx.readFile(filePath,{});
    } catch (error) {
        return res.status(400).send('Error reading Excel file: ' + error.message);
    }

    // Check if the workbook has any worksheets
    if (workbook.worksheets.length === 0) {
        return res.status(400).send('The uploaded Excel file does not contain any worksheets.');
    }

    var productList = []
    //read input info
    workbook.worksheets.forEach((worksheet,idx)=>{
        if(idx < 2){
        const rows = worksheet.getSheetValues().map((row,idx)=>{
            //把当前行号追加到行信息中
            return [row,idx]
        })

        if(idx === 0){
            //第一个表
            for(var i = 0; i < rows.length; i ++){
                const item = rows[i];
                if(!item){
                    continue;
                }
                var row = item[0];
                if(!row || row.length === 0 || row[1] === "" || row[1] === "SKU"){
                    continue;
                }else{
                    var productInfo = new ProductInfo()
                    row.forEach((value,column)=>{
                        if(worksheet.getCell(1,column).value === "SKU"){
                            productInfo.sku = value;
                        }else if(worksheet.getCell(1,column).value === "合同协议号"){
                            if(productInfo.contractNumber && productInfo.contractNumber !== ""){
                                productInfo.contractNumber1 = value;
                            }else{
                                productInfo.contractNumber = value;
                            }
                        }else if(worksheet.getCell(1,column).value === "单号"){
                            productInfo.orderNumber = value;
                        }else if(worksheet.getCell(1,column).value === "序号"){
                            productInfo.seqNumber = value;
                        }else if(worksheet.getCell(1,column).value === "件数"){
                            productInfo.packQty = value;
                        }else if(worksheet.getCell(1,column).value === "品名"){
                            productInfo.productName = value;
                        }else if(worksheet.getCell(1,column).value === "型号"){
                            productInfo.model = value;
                        }else if(worksheet.getCell(1,column).value === "颜色"){
                            productInfo.color = value;
                        }else if(worksheet.getCell(1,column).value === "数量"){
                            productInfo.qty = value;
                        }else if(worksheet.getCell(1,column).value === "单位"){
                            productInfo.unit = value;
                        }else if(worksheet.getCell(1,column).value === "单价"){
                            productInfo.price = value;
                        }else if(worksheet.getCell(1,column).value === "总价"){
                            productInfo.totalPrice = value;
                        }else if(worksheet.getCell(1,column).value === "品牌"){
                            productInfo.brand = value;
                        }else if(worksheet.getCell(1,column).value === "报关单价"){
                            productInfo.submitPrice = value;
                        }else if(worksheet.getCell(1,column).value === "净重"){
                            productInfo.pureWeight = value;
                        }else if(worksheet.getCell(1,column).value === "毛重"){
                            productInfo.weight = value;
                        }else if(worksheet.getCell(1,column).value === "利润点"){
                            productInfo.profitRate = value;
                        }else if(worksheet.getCell(1,column).value === "备注"){
                            productInfo.remark = value;
                        }else if(worksheet.getCell(1,column).value === "文件保存名"){
                            productInfo.savedFileName = value
                        }
                    })
                    productList.push(productInfo)
                }
            }
            productList = productList.filter((product,idx)=>{
                return product.sku !== ''
            })
        }else if(idx === 1){
            const newRows = rows.filter((row)=>{
                var testRow = row[0];
                return testRow[1] !== '' && testRow[1] !== 'SKU' && testRow[1] !== 'sku'
            })
            newRows.forEach((testRow,idx)=>{
                // sku	备注	账号	海关编码	品名	型号	单位	含税单价	每件个数	每件毛重	颜色
                var row = testRow[0]
                var sku = row[1]
                var realRowIdx = testRow[testRow.length - 1]
                var productInfos = productList.filter((product,idx)=>{
                    return product.sku === sku
                })
                if(productInfos && productInfos.length > 0){
                    productInfos.forEach((productInfo)=>{
                                            if(productInfo){
                    var headerRow = row;
                    var delta = 0;
                    //L2
                    while(headerRow[12] !== '品名'){
                        delta ++;
                        var lastIdx = realRowIdx - delta;
                        if(lastIdx < 0){
                            break;
                        }
                        headerRow = newRows(lastIdx)[0]
                    }

                    row.forEach((value,column)=>{
                        if(column <= 11){
                            const header = worksheet.getCell(1,column).value
                            // console.log(`heaeder is : ${header}`);
                            if( header === '备注' && productInfo.remark === ''){
                                productInfo.remark = `${value}`;
                            }else if(header === '账号' && productInfo.account === ''){
                                productInfo.account = `${value}`;
                            }else if(header === '海关编码'){
                                productInfo.customsCode = `${value}`;
                                // console.log(`海关编码: ${productInfo.customsCode}`)
                            }else if(header === '品名' && productInfo.productName === ''){
                                productInfo.productName = `${value}`;
                            }else if(header === '型号' && productInfo.model === ''){
                                productInfo.model = `${value}`;
                            }else if(header === '单位' && productInfo.unit === ''){
                                productInfo.unit = `${value}`;
                            }else if(header === '含税单价' && productInfo.priceWithTax === 0 && value !== undefined){
                                productInfo.priceWithTax = `${value}`;
                            }else if(header === '每件个数' && productInfo.qtyPerPack === 0 && value !== undefined){
                                productInfo.qtyPerPack = `${value}`;
                            }else if(header === '每件毛重' && productInfo.weightPerPack === 0 && value !== undefined){
                                productInfo.weightPerPack = `${value}`;
                            }else if(header === '颜色' && productInfo.color === ''){
                                productInfo.color = `${value}`;
                            } 
                        }
                    })
                    var descs = []
                    var nextRowIdx = realRowIdx + 1;
                    var nextRow = worksheet.getRow(nextRowIdx)
                    //品牌类型
                    var brandType = nextRow.values[15]
                    //品牌
                    var brand = nextRow.values[16]
                    //享惠
                    var benefit = nextRow.values[17]

                    descs.push(brandType)
                    descs.push(brand)
                    descs.push(benefit)
                        for(var col = 18; col<row.length; col ++){
                            //剩下的去找header
                            const header = headerRow[col];
                            const headerValue = nextRow.values[col];
                            const composedValue = `${header}:${headerValue}`;
                            if(headerValue && headerValue.length > 0 && header !== '其他申报要素'){
                                descs.push(composedValue)
                            }
                        }
                        const desc = descs.join("|")
                        productInfo.productDesc = desc

                            for(var col = 12; col<row.length; col ++){
                                //剩下的去找header
                                //品名1	海关HS编码1	检疫附加码	品牌类型1	品牌1	出口享惠情况1	用途	外观	是否成卷	是否单面自粘	成分	规格尺寸	型号	其他申报要素
                                const header = headerRow[col];
                                const headerValue = nextRow.values[col];

                                if( header === '检疫附加码' && productInfo.attachCode === ''){
                                    productInfo.attachCode = `${headerValue||""}`
                                }else if( header === '海关HS编码'){
                                    // console.log(`海关HS编码:${headerValue}`)
                                    productInfo.customsCode = `${headerValue}`
                                }else if( header === '用途' && productInfo.usedFor === ''){
                                    productInfo.usedFor = `${headerValue}`
                                }else if( header === '外观' && productInfo.look === ''){
                                    productInfo.look = `${headerValue}`
                                }else if( header === '是否成卷' && productInfo.isBolt === ''){
                                    productInfo.isBolt = `${headerValue}`
                                }else if( header === '是否单面自粘' && productInfo.isSinglePaste === ''){
                                    productInfo.isSinglePaste = `${headerValue}`
                                }else if( header === '成分' && productInfo.texture === ''){
                                    productInfo.texture = `${headerValue}`
                                }else if( header === '规格尺寸' && productInfo.size === ''){
                                    productInfo.size = `${headerValue}`
                                }else if( header === '其他申报要素' && productInfo.otherSubmitFactor === ''){
                                    productInfo.otherSubmitFactor = `${headerValue||""}`
                                }
                            }
                    }
                })
                }
            })
        }
        }
    })

    //分组
    var productGroup = []
    var lastProduct = undefined;
    productList.forEach((product,idx)=>{
        if(product.sku !== 'SKU' && product.sku !== 'sku'){
            if(lastProduct === undefined || lastProduct.seqNumber >= product.seqNumber){
                //如果上一个是空，或者当前商品序号不大于上一个商品序号, 则认定为新的一组商品
                var list = []
                list.push(product)
                productGroup.push(list);
            }else{
                var lastList = productGroup[productGroup.length - 1] || []
                lastList.push(product)
            }
            lastProduct = product;
        }
    })
    
    //商品组里面的每个列表为同一个报关单里面的商品
    const generateTime = moment().format("yyyy-MM-DD-HH-mm-ss")
    const outputFolder = path.resolve(__dirname,"..","output",generateTime);
    if(!fs.existsSync(outputFolder)){
        fs.mkdirSync(outputFolder,{recursive:true})
    }
    productGroup.forEach(async(prdList=[],idx)=>{   
        //每个prdlist 作为一个文件的数据来源
        if(prdList.length > 0){

            const newworkbook = new ExcelJS.Workbook();  
            const templateFilePath = path.resolve(__dirname,"..","template","template.xlsx")
            await newworkbook.xlsx.readFile(templateFilePath,) 
            //--------------报关单----------------
            const worksheetInfo = newworkbook.worksheets[0];


            worksheetInfo.getCell("V18").value = totalPureWeight;
            //总件数 = 每个商品件数相加
            const packQtyList = prdList.map((prd)=>{return prd.packQty})
            var totalPack = calculateSum(packQtyList)
            //总毛重
            const weightList = prdList.map((prd)=>{return prd.weight})
            var totalWeight = calculateSum(weightList)
            //总净重
            const pureWeightList = prdList.map((prd)=>{return prd.pureWeight})
            var totalPureWeight = calculateSum(pureWeightList)
            
            worksheetInfo.getCell("E12").value = totalPack;
            worksheetInfo.getCell("F12").value = totalWeight;
            worksheetInfo.getCell("G12").value = totalPureWeight;
            worksheetInfo.getCell("T18").value = totalPack;
            worksheetInfo.getCell("U18").value = totalWeight;
            worksheetInfo.getCell("V18").value = totalPureWeight;

            //从20行开始每3行为一个商品
            prdList.forEach((prd,idx)=>{
                //项号
                worksheetInfo.getCell(`A${20+idx*3}`).value = `${idx+1}`
                //商品编号
                worksheetInfo.getCell(`B${20+idx*3}`).value = prd.customsCode
                //商品名称
                worksheetInfo.getCell(`D${20+idx*3}`).value = prd.productName
                //规格及型号
                worksheetInfo.getCell(`D${21+idx*3}`).value = prd.productDesc
                //数量
                worksheetInfo.getCell(`G${22+idx*3}`).value = prd.qty
                //单位
                worksheetInfo.getCell(`H${22+idx*3}`).value = prd.unit
                //单价
                worksheetInfo.getCell(`I${20+idx*3}`).value = prd.submitPrice
                //总价
                worksheetInfo.getCell(`I${21+idx*3}`).value = prd.totalPrice
                //币制
                worksheetInfo.getCell(`I${22+idx*3}`).value = "美元"
                //原产国
                worksheetInfo.getCell(`K${20+idx*3}`).value = "中国"
                //最终目的国（地区）
                worksheetInfo.getCell(`M${20+idx*3}`).value = "美国"
                //境内货源地
                worksheetInfo.getCell(`P${20+idx*3}`).value = "深圳特区"
                //征免
                worksheetInfo.getCell(`S${20+idx*3}`).value = "照章征税"
                //净重	
                worksheetInfo.getCell(`T${20+idx*3}`).value = prd.pureWeight
                // 毛重	
                worksheetInfo.getCell(`U${20+idx*3}`).value = prd.weight
                // 件数
                worksheetInfo.getCell(`V${20+idx*3}`).value = prd.packQty



            })

            //--------------申报要素----------------
            const worksheetFactor = newworkbook.worksheets[1];
            //所有产品做个分类[{key:"9000030",list:[]}]
            var customProductList = []
            prdList.forEach((prdItem,index) => {
                const keys = customProductList.map((item)=>{
                    return item.key;
                }).filter((key)=>{
                    return key !== ''
                })
                if(!keys.includes(prdItem.customsCode)){
                    var goodsInSameCustomCode = []
                    goodsInSameCustomCode.push(prdItem)
                    var obj = {key:prdItem.customsCode,list:goodsInSameCustomCode}
                    customProductList.push(obj)
                }else{
                    var filteredResult = customProductList.filter((listObj)=>{
                        return listObj.key === prdItem.customsCode
                    })
                    var goodsListObj = filteredResult.length > 0 ? filteredResult[0]: []
                    var goodlist = goodsListObj.list;
                    goodlist.push(prdItem)
                } 
            });

            var lastGroupLength = 0;

            customProductList.forEach((grpObj,idx)=>{
                //相同编号的，设置一组表头
                const offset = idx === 0 ? 0 : 2;
                const grpHeaderIdx = 10+lastGroupLength+offset
                worksheetFactor.getCell(`A${grpHeaderIdx}`).value = "序号"
                worksheetFactor.getCell(`B${grpHeaderIdx}`).value = "品名"
                worksheetFactor.getCell(`C${grpHeaderIdx}`).value = "海关HS编码"
                worksheetFactor.getCell(`D${grpHeaderIdx}`).value = "检疫附加码"
                worksheetFactor.getCell(`E${grpHeaderIdx}`).value = "品牌类型"
                worksheetFactor.getCell(`F${grpHeaderIdx}`).value = "品牌"
                worksheetFactor.getCell(`G${grpHeaderIdx}`).value = "出口享惠情况"
                worksheetFactor.getCell(`H${grpHeaderIdx}`).value = "用途"
                worksheetFactor.getCell(`I${grpHeaderIdx}`).value = "材质"
                worksheetFactor.getCell(`J${grpHeaderIdx}`).value = "型号"
                worksheetFactor.getCell(`K${grpHeaderIdx}`).value = "其他申报要素"
                const arr = grpObj.list||[]
                arr.forEach((prd,item)=>{
                    const itemIdx = grpHeaderIdx+idx+1
                    worksheetFactor.getCell(`A${itemIdx}`).value = prd.seqNumber
                    worksheetFactor.getCell(`B${itemIdx}`).value = prd.productName
                    worksheetFactor.getCell(`C${itemIdx}`).value = prd.customsCode
                    // console.log(`海关HS编码2:${prd.customsCode}`)
                    worksheetFactor.getCell(`D${itemIdx}`).value = prd.attachCode
                    worksheetFactor.getCell(`E${itemIdx}`).value = prd.brandType
                    worksheetFactor.getCell(`F${itemIdx}`).value = prd.brand
                    worksheetFactor.getCell(`G${itemIdx}`).value = prd.exportBenefitSituation
                    worksheetFactor.getCell(`H${itemIdx}`).value = prd.usedFor
                    worksheetFactor.getCell(`I${itemIdx}`).value = prd.texture
                    worksheetFactor.getCell(`J${itemIdx}`).value = prd.model
                    worksheetFactor.getCell(`K${itemIdx}`).value = prd.otherSubmitFactor
                    //记录刚刚输入的一组数据的个数用来标识下组数据开始索引
                    lastGroupLength = customProductList.length;
                })
            })

            var outputFileName = prdList[0].savedFileName||"";
            if(outputFileName !== "文件保存名" && outputFileName !== ''){
                if(outputFileName && !outputFileName.endsWith(".xlsx")){
                    outputFileName = outputFileName+".xlsx";
                }
                const outFilePath = path.resolve(outputFolder,outputFileName)
                await newworkbook.xlsx.writeFile(outFilePath)
                console.log(`已输出 -> ${outFilePath}`)
            }
        }
        console.log(`已完成`)
    })
}    

module.exports = { transformExcel };